﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace NewLandOY20
{
    /// <summary>
    /// Access数据库连接类，用于连接或断开数据库，数据增删改查
    /// </summary>
    public class AccessHelper
    {
        OleDbConnection connection = null;
        OleDbTransaction transaction = null;

        string conn_str = "";

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="path">数据库文件所在路径</param>
        /// <param name="password">数据库连接密码</param>
        public AccessHelper(string path, string password)
        {
            this.conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Database Password= " + password;
        }

        /// <summary>
        /// 连接到数据库
        /// </summary>
        /// <returns></returns>
        public bool Connect()
        {
            try
            {
                if(connection != null)
                {
                    connection.Close();
                    connection = null;
                }
                connection = new OleDbConnection(@conn_str);
                connection.Open();
                if (connection != null)
                    return true;
                return false;
            }
            catch(OleDbException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public bool DisConnect()
        {
            try
            {
                if(connection != null)
                {
                    connection.Close();
                    connection = null;
                }
                return true;
            }
            catch(OleDbException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 在数据库内执行查询操作
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">OleDbParameter[]对象</param>
        /// <returns>DataTable对象，保存查询到的数据</returns>
        public DataTable ExecuteDataTable(string sql, OleDbParameter[] parameters)
        {
            try
            {
                using (OleDbCommand Command = new OleDbCommand(sql, connection))
                {
                    if(parameters != null)
                    {
                        Command.Parameters.AddRange(parameters);
                    }
                    OleDbDataAdapter adapter = new OleDbDataAdapter(Command);
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    return table;
                }
            }
            catch(OleDbException ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 执行增删改操作，并返回数据库内更改的行数
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <param name="parameters">OleDbParameter[]对象</param>
        /// <returns>int 数据库内更改的行数</returns>
        public int ExecuteNonQuery(string sql, OleDbParameter[] parameters)
        {
            int affectRows = 0;
            try
            {
                using (OleDbTransaction Transaction = connection.BeginTransaction())
                {
                    using (OleDbCommand Command = new OleDbCommand(sql, connection, Transaction))
                    {
                        if (parameters != null)
                        {
                            Command.Parameters.AddRange(parameters);
                        }
                        affectRows = Command.ExecuteNonQuery();
                    }
                    Transaction.Commit();
                }
            
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
                return -1;
            }

            return affectRows;
        }


        public void BeginTransaction()
        {
            try
            {
                transaction = connection.BeginTransaction();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void CommitTransaction()
        {
            try
            {
                transaction.Commit();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void RollbackTransaction()
        {
            try
            {
                transaction.Rollback();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
